import pymysql
"""
数据库的ip地址：8.148.229.201
账号：root
密码：123456
数据库名称：jiac
端口号：3306（默认的就可以不写）

"""
"""
# 链接数据库：db是数据库对象
db = pymysql.connect(user = 'root' ,password='123456',host='8.148.229.201',database='jiac',cursorclass =
pymysql.cursors.DictCursor)
# 创建游标：相当于我们输入完sql命令的回车
cursor = db.cursor()

# 查询
# cursor.execute('select * from student')
# 全部的数据 = cursor.fetchall()      # fetchall：返回符合sql条件的所有数据
# print(全部的数据)       # 打印查询结果
# one_r = cursor.fetchone()       # fetchone：返回符合sql条件的一条数据
# print(one_r)

#新增
# cursor.execute("insert into student(name,age) values('姐姐',26),('亚宁',19),('雅宁',95)")
# db.commit()     # 对数据库表有真实改动的，需要用数据库对象提交

# 查看有没有新增成功
# cursor.execute('select * from student where name = "姐姐"')
# one = cursor.fetchone()
# print(one)

# 修改
# cursor.execute("update student set name='班扎古plus' where name ='班长'")
# db.commit()     # 对数据库表有改动，用数据库对象 commit

# 查看有没有修改成功
# cursor.execute('select * from student where name = "班扎古plus"')
# one = cursor.fetchone()
# print(one)

# 删除
cursor.execute("delete from student where name='姐姐'")
db.commit()     # 用数据库对象提交

# 查看
cursor.execute("select * from student")
all_data = cursor.fetchall()
print(all_data)

# 关闭数据库
cursor.close()      # 先关闭游标
db.close()      # 再去关闭数据库对象
"""
"""
cursorclass=pymysql.cursors.DictCursor   # 可以将返回的对象变成字典
user= 'root'   # 输入数据库账号
password = '123456'  # 输入数据库密码
host = '8.148.229.201'      # 输入数据库ip地址
database = 'jiac'   # 要链接的jiac数据库
"""
class DB:
    def __init__(self,host,user,password,database):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
    # 连接数据库方法
    def connect(self):
        self.db = pymysql.connect(user = self.user,password=self.password,host=self.host,database=self.database,cursorclass =
        pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
    # 关闭数据库方法
    def close(self):
        self.cursor.close()
        self.db.close()
    def SeachAll(self,sql):
        # 先连接数据库
        self.connect()
        try:
            self.cursor.execute(sql)
            all_result = self.cursor.fetchall()
            return all_result
        except:     # 报错了，可能是sql写错
            self.db.rollback()      # 回滚方法
        finally:
            self.close()
    def SearchOne(self,sql):
        # 先连接数据库
        self.connect()
        try:
            self.cursor.execute(sql)
            all_result = self.cursor.fetchone()
            return all_result
        except:  # 报错了，可能是sql写错
            self.db.rollback()  # 回滚方法
        finally:
            self.close()
    # 封装主函数
    def __edit(self,sql):
        self.connect()
        result = 1
        try:
            self.cursor.execute(sql)
            self.db.commit()
            return result
        except:
            result = 0
            self.db.rollback()
            return result
        finally:
            self.close()
    # 新增
    def insert(self,sql):
        return self.__edit(sql)
    # 删除
    def delete(self,sql):
        return self.__edit(sql)
    # 修改
    def update(self,sql):
        return self.__edit(sql)
if __name__ == '__main__':
    # 测试类，调用一下
    """
    数据库的ip地址：8.148.229.201
    账号：root
    密码：123456
    数据库名称：jiac
    端口号：3306（默认的就可以不写）

    """
    d1 = DB("8.148.229.201",'root','123456','jiac')
    # print(d1.SeachAll("select * from student"))
    # print(d1.SearchOne("select * from student"))
    # d1.insert("insert into student(name,age) values('张三',19)")
    # print(d1.SearchOne("select * from student where name ='张三'"))
    # d1.update('update student set name="张山" where name = "张三"')
    # print(d1.SearchOne("select * from student where name ='张山'"))
    d1.delete("delete from student where name = '张山'")
    print(d1.SeachAll("select * from student"))